﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuantitativeIndicator.Tools
{
    class DataModel
    {
        public SqlConnection windConnection; // 数据库连接dd
        public SqlCommand windCommand; //数据库连接命令

        public DataModel()
        {
            this.windConnection = new SqlConnection("Server=localhost; User=sa; PWD=111111;database=DFZQ;Asynchronous Processing=true");
            this.windConnection.Open();
            this.windCommand = this.windConnection.CreateCommand();

        }
        public DataModel(string server,string user,string password,string database)
        {
            this.windConnection = new SqlConnection("Server=" +server+ "; User=" +user+ "; PWD="+ password +";database="+ database +";Asynchronous Processing=true");
            this.windConnection.Open();
            this.windCommand = this.windConnection.CreateCommand();
        }


        //得到交易日的列表
        public List<string> getTradingDateList(string startDate="20000101",string endDate="20111231")
        {
            windCommand.CommandText = "select * from tradingDates where tradingDate between '"+startDate+"' and '"+endDate+"'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            List<string> tradingDateList = new List<string>();
            while (sdr.Read())
            {
                tradingDateList.Add(sdr["tradingDate"].ToString());
            }
            sdr.Close();
            return tradingDateList;
        }

        //得到个股日收益率的序列
        public double[] getStockDailyIncreaseList(string startDate, string endDate, string code )
        {
            windCommand.CommandText = "SELECT [increase] FROM [DFZQ].[dbo].[stockDailyDerivative] where code='" + code + "' and tradingDate>=" + startDate + " and  tradingDate<=" + endDate + " order by tradingDate";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);
       
            double[] increaseList=new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                increaseList[i] =Double.Parse( dt.Rows[i]["increase"].ToString());
            }
            return increaseList;
        }

        //得到指数每日收益率序列
        public double[] getIndexDailyIncreaseList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT (closePrice-lastClosePrice)/lastClosePrice as increase  FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + code + "' and tradingDate>=" + startDate + " and  tradingDate<=" + endDate + " order by tradingDate";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] increaseList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                increaseList[i] = Double.Parse(dt.Rows[i]["increase"].ToString());
            }
            return increaseList;
        }
        

        //得到单个股票某日收盘价,如交易日不存在，则向前寻找最近一交易日的价格，还要考虑两种情况，股票是否上市/是否是股票？
        public double getStockClosePrice(string code, string tradingDate)
        {
            windCommand.CommandText = "SELECT  closePrice FROM [DFZQ].[dbo].[stockDaily] where code='"+code+"' and tradingDate='"+tradingDate+"'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double closePrice = 0;
            if (sdr.Read())
            {
                closePrice = Double.Parse(sdr["closePrice"].ToString());
                sdr.Close();
            }
            else
            {
                sdr.Close();
                windCommand.CommandText = "SELECT top 1  closePrice FROM [DFZQ].[dbo].[stockDaily] where code='" + code + "' and tradingDate<'" + tradingDate + "'  order by tradingDate desc ";
                 sdr = windCommand.ExecuteReader();
                 if (sdr.Read())
                 {
                     closePrice = Double.Parse(sdr["closePrice"].ToString());
                 }
                 sdr.Close();
            }
                
            return closePrice;
        }

        //得到某一个指数，某一日收盘价
        public double getIndexClosePrice(string indexCode, string tradingDate)
        {
            windCommand.CommandText = "SELECT  closePrice FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + indexCode + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double closePrice = 0;
            while (sdr.Read())
            {
                closePrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();

            return closePrice;
      
        }


        //得到单个股票某日收益率
        public double getStockDailyIncrease(string code, string tradingDate)
        {
            windCommand.CommandText = "SELECT  increase FROM [DFZQ].[dbo].[stockDailyDerivative] where code='" + code + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double increase = 0;
            while (sdr.Read())
            {
                increase = Double.Parse(sdr["increase"].ToString());
            }
            sdr.Close();

            return increase;
        }

        //得到单个股票的区间收益率,如果code不存在，则返回0;rtnMtd代表收益类型，"normal"代表正常收益，"log10"代表log10收益，"logE"代表ln收益
        public double getStockIntervalIncrease(string code, string startDate, string endDate,string rtnMtd)
        {
            double tmpIncrease=0;
            double startPrice = getStockClosePrice(code,startDate);
            double endPrice = getStockClosePrice(code, endDate);
            if (startPrice * endPrice == 0)
            {
                tmpIncrease = 0;
            }
            else
            {
                if (rtnMtd == "normal")
                {
                    tmpIncrease = endPrice / startPrice - 1;
                }
                else if (rtnMtd == "log10")
                {
                    tmpIncrease = Math.Log10(endPrice / startPrice);
                }
                else if (rtnMtd == "logE")
                {
                    tmpIncrease = Math.Log(endPrice / startPrice);
                }
            }
            return tmpIncrease;
        }

        //得到股票最后交易日
        public string getStockLatestTradedDate()
        {
            string tmpDate;
            windCommand.CommandText = "select top 1 tradingDate from stockDaily order by tradingDate desc";
            SqlDataReader sdr = windCommand.ExecuteReader();
            sdr.Read();
            tmpDate = sdr["tradingDate"].ToString();
            sdr.Close();
            return tmpDate;
        }


        //得到某一个指数，某一日收益率
        public double getIndexDailyIncrease(string indexCode, string tradingDate)
        {
            windCommand.CommandText = "SELECT  (closePrice-lastClosePrice)/lastClosePrice as increase  FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + indexCode + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double increase = 0;
            while (sdr.Read())
            {
                increase = Double.Parse(sdr["increase"].ToString());
            }
            sdr.Close();

            return increase;

        }

        //得到交易日的数量
        public int getTradedDatesNum(string startDate,string endDate)
        {
            int tmpNum;
            windCommand.CommandText = "select count(tradingDate) as cnt from dbo.tradingDates where tradingDate between "+startDate+" and "+endDate;
            SqlDataReader sdr = windCommand.ExecuteReader();
            sdr.Read();
            tmpNum = int.Parse(sdr["cnt"].ToString());
            sdr.Close();
            return tmpNum;
        }

   
       
    }
}
